“I want to think that there is someone with sound judgement at the controls” – Martin Feldstein

If you are a member of the website, click on the image below to view the webinar for this post.

(Note: Website members have access to the full webinar archive.)

In the first post on UserForms we looked at the general use of the UserForm.

In this post we are going to look at the individual VBA controls and how to use them. We will cover the most commonly used VBA controls. These are the Checkbox, ComboBox, CommandButton, Label, ListBox and TextBox. For each control, I will cover their most common uses and provide plenty of code examples you can use in your own projects.

We add controls to the UserForms to allow the user to make selections, enter text or click a button. To add a control to a UserForm we do the following

    The following table shows a list of the common controls

The screenshot below shows the three important Windows when adding controls to a UserForm. These are

UserForm Windows

    If you click on any control or the UserForm itself you will see the properties of the selected item displayed in the Properties window. You can change the name, caption etc. in this Window.

To change the name of the UserForm do the following

You can view the code of the UserForm in the following ways  

The following table shows the most commonly used functions that are available to all controls.

    The CheckBox is a simple control that allows the user set something to on or off. You will often see them used on web pages where you are asked to accept terms and conditions.

We can turn the CheckBox on or off by setting it to true or false

If we want to create an action when the user clicks on the checkbox then we create a checkbox event. This is simply a sub that runs when the checkbox is clicked.

    To create this event simply click on the checkbox in design mode and you will get the following

    The following code shows an example of how we use it

The label is the most simple control. Generally speaking, we don’t refer to it in the code. It is used to label the controls or display a short notice.

You can set the text of the Label in the code using the Caption property

The textbox is used to allows the user to enter text. We can read or write from a text box as follows

    The textbox has properties Text and Values. These are the same thing.

From MSDN: For a TextBox, any value you assign to the Text property is also assigned to the Value property.

    The problem with the text box is that the user can enter anything. If the user is entering basic text then this is fine. If the text is to be used for a calculation or for looking up something then we need to validate it.

For example, if the user is going to pick a year between 2010 and 2019 we should use a ComboBox/Listbox that only contains valid years. Then we don’t need to validate the format and range of the user’s entry.

The following code prevents the user entering anything other than a number in the textbox

If you the user to select a date you can use the MonthView control. It is one of the additional controls that comes with Visual Basic. It works quite well and looks like the standard date picker you see in most applications.

To add the MonthView control:

    To get the user selection from the MonthView control you can use the DateClick event as the following code shows

    For more information on the MonthView see these links:

Issues adding the MonthView – see top answer on this StackOverflow page

MSDN – Using the MonthView Control

    The ComboBox is used to allow the user to select an item from a list. It is very similar to the listbox. The main difference is the listbox allows multiple selections.

In most cases we want to do four things with the ComboBox\ListBox

We can fill the combobox one item at at a time using the AddItem property.

    You would normally use AddItem in a loop where you are checking each item before adding it

If you want to fill the ComboBox from an entire range you can do it in one line of code

    Normally when you fill a ComboBox you want to clear the existing contents first

If our range has multiple entries then we want to avoid adding the same item multiple times. We can do this using a Dictionary

The easiest way to show how these work is with an example. Imagine we have a list of countries and their capitals in cells A1:B196.

    We want the user to select any country. When they do our UserForm will display the capital of that country. The screenshot below shows and example of this

    The first thing we want to do is fill the countries ComboBox when the form loads. We do this using the UserForm_Initialize event which we looked at in the first post on VBA UserForms.

    We can use the following code to fill the ComboBox from a range and set the selected item to be the first one. (Note we don’t need to clear the ComboBox here as the Initialize event is only used once – when the UserForm is created.)

    When the user selects a country we want to display the capital city in the textbox. We use the Change Event of the ComboBox. To create this we simply double-click on the ComboBox and it will be automatically created.

    When the user clicks Ok we write the selected values to the Results worksheet

A very commmon task to perform is to update a second ComboBox based on the selection of the first.

    Imagine we have two ComboBoxes – one contains the name of a country and one has a list of three cities from that country.

    When the user selects a country we update the city ComboBox with the cities from that country.

    Our data is stored as follows

    Anytime the Country ComboBox value is set to a country we update the City ComboBox to contain the three cities from that country. This happens in two places

    The code for these is as follows

    In both cases we call our FillCityCombo Sub to fill the city ComboBox. It takes one parameter which is the position of the current country selection.

We use the position value to count from the top row of our worksheet range.

The ListBox is used in almost the same way as the ComboBox. The code in the ComboBox section above will work for the ListBox also.

We can use the ListBox the same way as we used the ComboBox. The difference is how they are displayed

    The other major difference between them is that the ListBox allows multiple selections and the ComboBox doesn’t.

    We can get all the selected items in the ListBox by reading through all the items and checking if each one is selected. In the code below we add all the selected items to a Collection.

    That concludes the two posts on UserForm(see first one here).

I hope you found them of benefit. If you have any questions or queries please feel free to add a comment or email me at [email protected].

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)